﻿Public Class CsvConnector
    Public SourceDt As DataTable
    Public FilePath As String = ""
    Public Separator As String = ";"
    Public FieldList As List(Of String)


    Public Sub New(ByVal FilePathToCsv As String, ByVal Delimiter As String)
        Me.FilePath = FilePathToCsv
        Me.Separator = Delimiter
        ReadCsv(FilePathToCsv, Delimiter)
    End Sub

    Private Sub ReadCsv(ByVal FilePath As String, ByVal Separator As String)

        Dim csvDt As DataTable = New DataTable("CsvTable")
        Dim i As Integer
        Dim myRow As DataRow
        Dim fieldValues As String()
        Dim myReader As IO.StreamReader

        Try
            myReader = IO.File.OpenText(FilePath)
            fieldValues = myReader.ReadLine().Split(Separator)

            If IsNothing(FieldList) Then
                FieldList = New List(Of String)
            Else
                FieldList.Clear()
            End If

            For i = 0 To fieldValues.Length() - 1
                csvDt.Columns.Add(New DataColumn(fieldValues(i)))
                FieldList.Add(fieldValues(i))
            Next

            While myReader.Peek() <> -1
                fieldValues = myReader.ReadLine().Split(Separator)
                myRow = csvDt.NewRow
                For i = 0 To fieldValues.Length() - 1
                    If Not fieldValues(i).ToString = "NULL" Then
                        myRow.Item(i) = fieldValues(i).ToString
                    Else
                        myRow.Item(i) = ""
                    End If

                Next
                csvDt.Rows.Add(myRow)
            End While

            SourceDt = csvDt
        Catch ex As Exception
            MsgBox("Error reading from CSV file: " & ex.Message & Environment.NewLine, MsgBoxStyle.Critical, "Error")
            SourceDt = New DataTable("Empty")
        Finally
            If Not IsNothing(myReader) Then
                myReader.Close()
            End If
        End Try


    End Sub
End Class
